﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;

namespace NVR.DALSqlProvider
{
    public class DALUserInfoSqlProvider:IDAL.IDALUserInfo
    {
        #region IDALUserInfo 数据表的基本操作

        public int Insert(NVR.Entity.userInfo obj)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_userInfo_insert") as SqlCommand;
            cmd.Parameters.AddWithValue("@username", obj.username == String.Empty ? DBNull.Value : (object)obj.username);
            cmd.Parameters.AddWithValue("@pwd", obj.pwd == String.Empty ? DBNull.Value : (object)obj.pwd);
            cmd.Parameters.AddWithValue("@regDate", obj.regDate == DateTime.Now ? DateTime.Now : (object)obj.regDate);
            cmd.Parameters.AddWithValue("@email", obj.email == String.Empty ? DBNull.Value : (object)obj.email);
            cmd.Parameters.AddWithValue("@qq", obj.qq == String.Empty ? DBNull.Value : (object)obj.qq);
            cmd.Parameters.AddWithValue("@tel", obj.tel == String.Empty ? DBNull.Value : (object)obj.tel);
            cmd.Parameters.AddWithValue("@purview", obj.purview == 0 ? DBNull.Value : (object)obj.purview);
            cmd.Parameters.AddWithValue("@parentid", obj.parentid == 0 ? DBNull.Value : (object)obj.parentid);
            return db.ExecuteNonQuery(cmd);
        }

        public int Update(NVR.Entity.userInfo obj)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_userInfo_update") as SqlCommand;
            cmd.Parameters.AddWithValue("@username", obj.username == String.Empty ? DBNull.Value : (object)obj.username);
            cmd.Parameters.AddWithValue("@pwd", obj.pwd == String.Empty ? DBNull.Value : (object)obj.pwd);
            cmd.Parameters.AddWithValue("@registerDate", obj.regDate);
            cmd.Parameters.AddWithValue("@email", obj.email == String.Empty ? DBNull.Value : (object)obj.email);
            cmd.Parameters.AddWithValue("@qq", obj.qq == String.Empty ? DBNull.Value : (object)obj.qq);
            cmd.Parameters.AddWithValue("@tel", obj.tel == String.Empty ? DBNull.Value : (object)obj.tel);
            cmd.Parameters.AddWithValue("@purview", obj.purview == 0 ? DBNull.Value : (object)obj.purview);
            return db.ExecuteNonQuery(cmd);
        }

        public int Delete(NVR.Entity.userInfo obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_userInfo_delete") as SqlCommand;

            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@username", obj.username);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }

        public List<NVR.Entity.userInfo> LoadAll()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string strSql = "select *from userInfo";
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            var reader = cmd.ExecuteReader();
            List<NVR.Entity.userInfo> list = new List<NVR.Entity.userInfo>();
            while (reader.Read())
            {
                NVR.Entity.userInfo user = new NVR.Entity.userInfo();
                user.username = reader["username"] is DBNull ? String.Empty : (String)reader["username"];
                user.pwd = reader["pwd"] is DBNull ? String.Empty : (String)reader["pwd"];
                user.regDate = reader["regDate"] is DBNull ? DateTime.Now : (DateTime)reader["regDate"];
                user.email = reader["email"] is DBNull ? String.Empty : (String)reader["email"];
                user.qq = reader["qq"] is DBNull ? String.Empty : (String)reader["qq"];
                user.tel = reader["tel"] is DBNull ? String.Empty : (String)reader["tel"];
                user.purview = reader["purview"] is DBNull ? 0 : (Int32)reader["purview"];
                list.Add(user);
            }
            return list;
        }

        public NVR.Entity.userInfo LoadById(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_userInfo_selectByID") as SqlCommand;
            cmd.Parameters.AddWithValue("@username", username);
            var reader = db.ExecuteReader(cmd);
            Entity.userInfo entity = null;

            while (reader.Read())
            {
                entity = new Entity.userInfo();
                entity.username = reader["username"] is DBNull ? String.Empty : (String)reader["username"];
                entity.pwd = reader["pwd"] is DBNull ? String.Empty : (String)reader["pwd"];
                entity.regDate = reader["regDate"] is DBNull ? DateTime.Now : (DateTime)reader["regDate"];
                entity.email = reader["email"] is DBNull ? String.Empty : (String)reader["email"];
                entity.qq = reader["qq"] is DBNull ? String.Empty : (String)reader["qq"];
                entity.tel = reader["tel"] is DBNull ? String.Empty : (String)reader["tel"];
                entity.purview = reader["purview"] is DBNull ? 0 : (Int32)reader["purview"];
            }
            return entity;
        }
        #endregion

        #region IDALUserInfo 成员
        #endregion
    }
}
